package br.com.wuo.karina.model.dao.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Properties;

import br.com.wuo.karina.model.Turma;
import br.com.wuo.karina.model.dao.TurmaDao;


public class TurmaDaoImpl implements TurmaDao{
	
	private Connection conn;
	
	public TurmaDaoImpl(){
		try{
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			String database = "jdbc:odbc:Wuo";
			Properties props = new Properties();
			props.put ("charSet", "UTF-8");
			conn = DriverManager.getConnection(database, props);
			
		}catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
        
	}
	
	
	public void SalvarTurma (Turma turma) throws SQLException{
			
		String insertTurma = "INSERT INTO Turma (Nome, Status) VALUES (?, ?)";
		
		PreparedStatement stmt = conn.prepareStatement(insertTurma);
		
		stmt.setString(1, turma.getNome());
		stmt.setString(2, "A");
		
		stmt.execute();
				
	};
	
	public Turma PesquisarTurma (int cod) throws SQLException{
		
		String queryTurma = "SELECT Nome FROM Turma WHERE Cod = ? AND Status = ?";
		
		PreparedStatement stmt = conn.prepareStatement(queryTurma);
		
		stmt.setInt(1, cod);
		stmt.setString(2, "A");
		
		ResultSet rs = stmt.executeQuery();
		
		rs.next();
		String Nome = rs.getString("Nome");
		
		Turma turma = new Turma(cod, Nome);
		
		return turma;
	};
	
	public void DeletarTurma (int cod) throws SQLException{
		String deleteTurma = "UPDATE Turma SET Status = ? WHERE cod = ? ";
		
		PreparedStatement stmt = conn.prepareStatement(deleteTurma);
		stmt.setString(1, "I");
		stmt.setInt(2, cod);
		
		stmt.executeUpdate();
	};
	
	public void AlterarTurma (Turma turma) throws SQLException{
		String updateTurma = "UPDATE Turma SET Nome = ? WHERE cod = ?";
		
		PreparedStatement stmt = conn.prepareStatement(updateTurma);
		
		stmt.setString(1, turma.getNome());
		stmt.setInt(2, turma.getCodigo());
		
		stmt.executeUpdate();
		
	};
	
	public ArrayList<Turma> getAllTurmas() throws SQLException{
		ArrayList<Turma> turmas = new ArrayList<>();
		
		String query = "SELECT Cod, Nome FROM Turma WHERE Status = ? ";
		
		PreparedStatement stmt = conn.prepareStatement(query);
		stmt.setString(1, "A");
		
		ResultSet rs = stmt.executeQuery();
		
		while(rs.next()){
			int codigo = rs.getInt("Cod");
			String nome = rs.getString(2);
			
			Turma turma = new Turma(codigo, nome);
			turmas.add(turma);
		}
		
		return turmas;
		
	}


	@Override
	public ArrayList<String> getAlunosDaTurma(int codigo) throws SQLException {
		ArrayList<String> alunosDaTurma = new ArrayList<>();
		
		String query = "SELECT apt.Cod_Aluno, a.Nome, a.Sobrenome " +
					   "FROM Alunos_Por_Turma apt,  " +
					   		"Alunos a " +
					   "WHERE apt.Cod_Aluno = a.Cod " +
					   	 "AND Cod_Turma = ?";
		
		PreparedStatement stmt = conn.prepareStatement(query);
		stmt.setInt(1, codigo);
		
		ResultSet rs = stmt.executeQuery();
		
		while(rs.next()){
			int cod = rs.getInt(1);
			String nome = rs.getString(2);
			String sobrenome = rs.getString(3);
			
			String itemAluno = cod + " - " + nome + " " + sobrenome;
			
			alunosDaTurma.add(itemAluno);
		}
		
		return alunosDaTurma;
	}


	@Override
	public void AtribuirAlunos(Turma turma) throws SQLException {
		ArrayList<Integer> alunos = turma.getAlunos();
		
		String query = " INSERT INTO Alunos_Por_Turma (Cod_Aluno, Cod_Turma) VALUES (?, ?)";
		PreparedStatement stmt = conn.prepareStatement(query);
		
		for(int aluno:alunos){
			stmt.setInt(1, aluno);
			stmt.setInt(2, turma.getCodigo());
			
			stmt.executeUpdate();			
		}
	};
	
}
